﻿using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;



/// <summary>

///DB_class 的摘要说明

/// </summary>

public class DB_class
{

    SqlConnection myconn;

    SqlCommand mycmd;

    DataSet ds; //数据集

    SqlDataAdapter adapt;

    public DB_class()

    {

        //

        //TODO: 在此处添加构造函数逻辑

        //

    }

    public SqlConnection GetConnection()

    {

        string mystr = "Server=192.168.10.58;Database=GenyeeMesERP_XM_Data;User id=sa;password=tizi@123123"; //tizit@123123

        SqlConnection myconn = new SqlConnection(mystr);

        return myconn;

    }



    public int ExecNonQuery(string strSql)

    {



        try

        {

            myconn = GetConnection();//与数据库连接

            mycmd = new SqlCommand();//初始化SqlCommand类对象

            mycmd.Connection = myconn;

            mycmd.CommandText = strSql;

            if (mycmd.Connection.State != ConnectionState.Open)

            {

                mycmd.Connection.Open();//打开与数据库的连接

            }

            int a = mycmd.ExecuteNonQuery();//执行Sql操作，并返回受影响的行数

            return a;



        }

        catch (Exception ex)

        {

            throw new Exception(ex.Message, ex);



        }

        finally

        {

            if (mycmd.Connection.State == ConnectionState.Open)

            {//断开连接，释放资源

                mycmd.Connection.Close();

                myconn.Dispose();

                mycmd.Dispose();



            }

        }

    }



    public string ExecScalar(string strSql)

    {

        try

        {

            myconn = GetConnection();//与数据库连接

            mycmd = new SqlCommand();//初始化SqlCommand类对象

            mycmd.Connection = myconn;

            mycmd.CommandText = strSql;

            if (mycmd.Connection.State != ConnectionState.Open)

            {

                mycmd.Connection.Open();//打开与数据库的连接

            }

            //使用SqlCommand对象的ExecuteScalar方法返回第一行第一列的值

            //strSql = Convert.ToString(mycmd.ExecuteScalar());

            string another = Convert.ToString(mycmd.ExecuteScalar());

            return another;

        }

        catch (Exception ex)

        {

            throw new Exception(ex.Message, ex);



        }

        finally

        {

            if (mycmd.Connection.State == ConnectionState.Open)

            {//断开连接，释放资源

                myconn.Dispose();

                mycmd.Connection.Close();

                mycmd.Dispose();



            }

        }

    }



    public DataTable GetDataSet(string strSql, string TableName)

    {

        ds = new DataSet();

        try

        {

            myconn = GetConnection();//与数据库连接

            adapt = new SqlDataAdapter(strSql, myconn); //实例化SqlDataAdapter类对象

            adapt.Fill(ds, TableName);//填充数据集

            return ds.Tables[TableName];//返回数据集DataSet的表的集合



        }

        catch (Exception ex)

        {

            throw new Exception(ex.Message, ex);



        }

        finally

        {//断开连接，释放资源

            myconn.Close();

            adapt.Dispose();

            ds.Dispose();

            myconn.Dispose();

        }



    }

}